package com.huiren.util;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.cxf.common.util.StringUtils;
import org.apache.log4j.Logger;

public class JdbcUtil {
	private static Logger logger = Logger.getLogger(JdbcUtil.class);

	public static String test(String driver, String url, String user,
			String password) {
		if (StringUtils.isEmpty(driver))
			return "数据库类型不能为空";
		if (StringUtils.isEmpty(url))
			return "数据库类型不能为空";
		if (StringUtils.isEmpty(user))
			return "用户名不能为空";
		if (StringUtils.isEmpty(password))
			return "密码不能为空";

		Connection conn = null;
		try {
			Class.forName(driver).newInstance();
		} catch (Exception ex) {
			logger.error(ex);
			return ex.getMessage();
		}
		try {
			conn = DriverManager.getConnection(url, user, password);
			return "链接成功";
		} catch (Exception e) {
			logger.error(e);
			return e.getMessage();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
	}

	public static Connection getConnection() throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		PropertiesUtil propertie = new PropertiesUtil();
		String driver = propertie.getValue("jdbc.driver");
		String url = propertie.getValue("jdbc.driverUrl");
		String user = propertie.getValue("jdbc.user");
		String password = propertie.getValue("jdbc.password");
		Connection conn = null;
		Class.forName(driver).newInstance();
		conn = DriverManager.getConnection(url, user, password);
		return conn;

	}

	public static int executeUpdate(String sql) {
		Statement stmt = null;
		Connection conn = null;
		int result = 0;
		try {
			stmt = getConnection().createStatement();
			logger.info(sql);
			result = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			logger.error(e);
			result = -1;
		} catch (InstantiationException e) {
			logger.error(e);
			result = -1;
		} catch (IllegalAccessException e) {
			logger.error(e);
			result = -1;
		} catch (ClassNotFoundException e) {
			logger.error(e);
			result = -1;
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return result;
	}

	public static int executeUpdate(List<String> sqls) {
		Statement stmt = null;
		Connection conn = null;
		int result = 0;
		try {
			stmt = getConnection().createStatement();
			for (String sql : sqls) {
				logger.info(sql);
				stmt.addBatch(sql);
			}
			result = stmt.executeBatch().length;
		} catch (SQLException e) {
			logger.error(e);
			result = -1;
		} catch (InstantiationException e) {
			logger.error(e);
			result = -1;
		} catch (IllegalAccessException e) {
			logger.error(e);
			result = -1;
		} catch (ClassNotFoundException e) {
			logger.error(e);
			result = -1;
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return result;
	}

	public static List<Map<String, Object>> executeQuery(String sql) {
		Statement stmt = null;
		Connection conn = null;
		ResultSet result = null;
		List<Map<String, Object>> list = null;
		try {
			stmt = getConnection().createStatement();
			result = stmt.executeQuery(sql);
			logger.info(sql);
			ResultSetMetaData rsmd = result.getMetaData();
			int count = rsmd.getColumnCount();
			list = new ArrayList<Map<String, Object>>();
			while (result.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= count; i++)
					if (result.getObject(i) instanceof CLOB)
						map.put(rsmd.getColumnName(i).toLowerCase(),
								clobToString((CLOB) result.getObject(i)));
					else
						map.put(rsmd.getColumnName(i).toLowerCase(),
								result.getObject(i));

				list.add(map);
			}

		} catch (SQLException e) {
			logger.error(e);
		} catch (InstantiationException e) {
			logger.error(e);
		} catch (IllegalAccessException e) {
			logger.error(e);
		} catch (ClassNotFoundException e) {
			logger.error(e);
		} finally {
			try {
				if (result != null)
					result.close();
				if (conn != null)
					conn.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return list;
	}

	// 获取当前的数据库类型
	public static String getJdbcType() {
		String type = null;
		PropertiesUtil propertie = new PropertiesUtil();
		if ("oracle.jdbc.driver.OracleDriver".equals(propertie
				.getValue("jdbc.driver")))
			type = "oracle";
		else if ("net.sourceforge.jtds.jdbc.Driver".equals(propertie
				.getValue("jdbc.driver")))
			type = "sqlserver";
		else if ("com.mysql.jdbc.Driver".equals(propertie
				.getValue("jdbc.driver")))
			type = "mysql";
		return type;
	}

	// oracle.sql.Clob类型转换成String类型
	public static String clobToString(CLOB clob) {
		String reString = "";
		Reader is = null;
		try {
			is = clob.getCharacterStream();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 得到流
		BufferedReader br = new BufferedReader(is);
		String s = null;
		try {
			s = br.readLine();
		} catch (IOException e) {
			e.printStackTrace();
		}
		StringBuffer sb = new StringBuffer();
		while (s != null) {
			sb.append(s);
			try {
				s = br.readLine();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		reString = sb.toString();
		return reString;
	}

}